package th.co.exat.uap.model;

import com.channgam.dbmlib.sql.MySQLJDBC;
import com.channgam.dbmlib.sql.iDataAdapter;
import java.util.ArrayList;
import java.util.List;
import th.co.exat.uap.config.Config;

public class Stations {

    private static iDataAdapter i;
    // Attribute Table Line;
    private String lineId;
    private String lineName;
    private String lineType;
    //Attribute Table Station
    private String stationId;
    private String stationName;
    private String stationUserCreate;
    private String stationTimeCreate;
    private String stationUserEdit;
    private String stationTimeEdit;

    public static Stations getStationById(String StationId) throws Exception {
        Stations s = new Stations();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_station "
                + "WHERE station_id = '" + StationId + "' ";
        i.query(sql);

        while (i.resultNext()) {
            s.setStationId(i.result().getString("station_id"));
            s.setLineId(i.result().getString("line_id"));
            s.setStationName(i.result().getString("station_name"));
        }

        i.close();
        return s;
    }

    public static Stations getLineByStationId(String StationId) throws Exception {
        Stations s = new Stations();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT l.id AS line_id , l.line_name "
                + "FROM uap_mst_line l, uap_mst_station s "
                + "WHERE l.id = s.line_id " +
                "AND station_id = '" + StationId + "' ";
        i.query(sql);

        while (i.resultNext()) {
            s.setLineId(i.result().getString("line_id"));
            s.setLineName(i.result().getString("line_name"));
        }

        i.close();
        return s;
    }

    public static List<Stations> getStationList() throws Exception {
        List<Stations> list = new ArrayList<Stations>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_station "
                + "ORDER BY station_id ";
        i.query(sql);

        while (i.resultNext()) {

            Stations l = new Stations();
            l.setStationId(i.result().getString("station_id"));
            l.setLineId(i.result().getString("line_id"));
            l.setStationName(i.result().getString("station_name"));
            list.add(l);
        }

        i.close();
        return list;
    }

    public static List<Stations> getLineList() throws Exception {
        List<Stations> list = new ArrayList<Stations>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_line "
                + "WHERE 1 ";
        i.query(sql);

        while (i.resultNext()) {

            Stations l = new Stations();
            l.setLineId(i.result().getString("id"));
            l.setLineName(i.result().getString("line_name"));
            list.add(l);
        }

        i.close();
        return list;
    }

    public static List<Stations> getExitStationList(String lineId) throws Exception {
        List<Stations> list = new ArrayList<Stations>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_station s "
                + "WHERE s.line_id = '" + lineId + "' "
                + "AND s.station_id in( "
                + "SELECT exit_station_id FROM uap_mst_charge)";
        i.query(sql);

        while (i.resultNext()) {

            Stations l = new Stations();
            l.setStationId(i.result().getString("station_id"));
            l.setStationName(i.result().getString("station_name"));
            l.setLineId(i.result().getString("line_id"));
            list.add(l);
        }

        i.close();
        return list;
    }

    public static List<Stations> getEnterStationList(String exitStationId) throws Exception {
        List<Stations> list = new ArrayList<Stations>();
        i = new MySQLJDBC(Config.getMySqlHost(),
                Config.getMySqlUsername(),
                Config.getMySqlPassword(),
                Config.getMySqlDbname());
        i.connect();
        String sql = "SELECT * "
                + "FROM uap_mst_station s "
                + "WHERE s.station_id in( "
                + "SELECT enter_station_id FROM uap_mst_charge "
                + "WHERE exit_station_id = '" + exitStationId + "')";
        i.query(sql);

        while (i.resultNext()) {

            Stations l = new Stations();
            l.setStationId(i.result().getString("station_id"));
            l.setStationName(i.result().getString("station_name"));
            l.setLineId(i.result().getString("line_id"));
            list.add(l);
        }

        i.close();
        return list;
    }

    public String getLineId() {
        return lineId;
    }

    public void setLineId(String lineId) {
        this.lineId = lineId;
    }

    public String getLineName() {
        return lineName;
    }

    public void setLineName(String lineName) {
        this.lineName = lineName;
    }

    public static iDataAdapter getI() {
        return i;
    }

    public static void setI(iDataAdapter i) {
        Stations.i = i;
    }

    public String getLineType() {
        return lineType;
    }

    public void setLineType(String lineType) {
        this.lineType = lineType;
    }

    public String getStationId() {
        return stationId;
    }

    public void setStationId(String stationId) {
        this.stationId = stationId;
    }

    public String getStationName() {
        return stationName;
    }

    public void setStationName(String stationName) {
        this.stationName = stationName;
    }

    public String getStationTimeCreate() {
        return stationTimeCreate;
    }

    public void setStationTimeCreate(String stationTimeCreate) {
        this.stationTimeCreate = stationTimeCreate;
    }

    public String getStationTimeEdit() {
        return stationTimeEdit;
    }

    public void setStationTimeEdit(String stationTimeEdit) {
        this.stationTimeEdit = stationTimeEdit;
    }

    public String getStationUserCreate() {
        return stationUserCreate;
    }

    public void setStationUserCreate(String stationUserCreate) {
        this.stationUserCreate = stationUserCreate;
    }

    public String getStationUserEdit() {
        return stationUserEdit;
    }

    public void setStationUserEdit(String stationUserEdit) {
        this.stationUserEdit = stationUserEdit;
    }
}
